package cn.edu.scau.daoImpl;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import cn.edu.scau.dao.WorkDao;
import cn.edu.scau.entity.Work;

@Repository("workDaoImpl")
public class WorkDaoImpl extends BaseDaoImpl<Work> implements WorkDao {

	@Override
	public Long findCountByColumnIDFront(Integer columnID) {

		Object[] objs = new Object[2];
		objs[0] = columnID;
		objs[1] = true;
		List<?> list = findByHql("select count(*) from Work w where w.columnId = ?0 and w.status =?1", objs);
		if (list != null && list.size() > 0) {
			return (Long) list.get(0);
		}
		return (long) 0;
	}

	@Override
	public Long findCountByColumnIDManagement(Integer columnID) {

		Object[] objs = new Object[1];
		objs[0] = columnID;
		List<?> list = findByHql("select count(*) from Work w where w.columnId = ?0", objs);
		if (list != null && list.size() > 0) {
			return (Long) list.get(0);
		}
		return (long) 0;
	}

	@Override
	public List getPropertyOfWorkFront(Integer columnID, Integer page, Integer pageSize) {

		String sql = "SELECT * FROM " + "(SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNum, * "
				+ "FROM [Scauaie.com].[dbo].[Works] where columnID = " + columnID + " and Status = 1 ) AS sub "
				+ "WHERE sub.RowNum BETWEEN " + (page - 1) * pageSize + 1 + " AND " + page * pageSize;
		Session session = getSessionFactory().openSession();
		Query query = session.createSQLQuery(sql).addEntity(Work.class);
		List list = query.list();
		session.flush();
		session.close();

		return list;

	}

	@Override
	public List getPropertyOfWorkManagement(Integer columnID, Integer page, Integer pageSize) {

		String sql = "SELECT * FROM " + "(SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNum, * "
				+ "FROM [Scauaie.com].[dbo].[Works] where columnID = " + columnID + " ) AS sub "
				+ "WHERE sub.RowNum BETWEEN " + (page - 1) * pageSize + 1 + " AND " + page * pageSize;
		Session session = getSessionFactory().openSession();
		Query query = session.createSQLQuery(sql).addEntity(Work.class);
		List list = query.list();
		session.flush();
		session.close();
		return list;

	}

}
